﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Xml;
using CoreLab.Oracle;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Workflows.Components.Entities.Providers;

namespace Workflows.Components.Entities.Relations.DirectAssociations
{
    public class EntityLinkageWorker:IWorker
    {
        #region fields
        private Thread _WorkerThread;
        private EntityLinkageInstruction _Instruction;
        private EntityLinkageOutput _Output;
        private Guid _JobTicketID;
        private RunContext _Context;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }
        #endregion

        #region IWorker Members

        public Thread WorkerThread
        {
            get { return _WorkerThread; }
        }

        public InstructionBase Instruction
        {
            get { return _Instruction; }
        }

        public ResultBase Output
        {
            get { return _Output; }
        }

        public Type ComponentType
        {
            get { return typeof(EntityLinkageComponent); }
        }

        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }

        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._JobTicketID = ticketGuid;
            this._Instruction = (EntityLinkageInstruction)request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }

        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._JobTicketID = ticketGuid;
            this._Instruction = (EntityLinkageInstruction)Instruction;
            this._Context = context;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }

        public void DoWork()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._JobTicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = Thread.CurrentThread;
            this.LinkEntities();
        }


        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._JobTicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = new Thread(new ThreadStart(this.LinkEntities));
            this._WorkerThread.Start();
        }

        public void CancelWork()
        {
            if (this._WorkerThread != null && this._WorkerThread.IsAlive)
            {
                this._WorkerThread.Abort();
                if (this.WorkerCancelledEvent != null)
                {
                    this.WorkerCancelledEvent(this._JobTicketID, this.GetType(), this._Instruction);
                }
            }

        }

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        #endregion

        #region work
        private void LinkEntities()
        {
            List<int> seedEntityIDs = new List<int>();
            EntityProvider provider =
                    new EntityProvider(
                        this._Instruction.ParentDbProvider,
                        this._Instruction.ParentConnStr,
                        this._Instruction.ParentSql,
                        this._Instruction.ParentKeyFieldName,
                        string.Empty);
            seedEntityIDs = provider.GetEntityIDs();

            // create stored proc and populate parameters 
            TableColumn[] cols = 
                DbUtil.GetTableColumns(
                    this._Instruction.TgtDbProvider, 
                    this._Instruction.TgtConnStr, 
                    string.Empty, this._Instruction.TgtTable);
            Dictionary<string, TableColumn> fieldToColMappings = new Dictionary<string, TableColumn>();
            foreach (TableColumn col in cols)
            {
                if (col.ColumnName.ToLower() == this._Instruction.TgtSrcKeyField.ToLower())
                {
                    fieldToColMappings.Add(this._Instruction.TgtSrcKeyField, col);
                }
                else if (col.ColumnName.ToLower() == this._Instruction.TgtRefKeyField.ToLower())
                {
                    fieldToColMappings.Add(this._Instruction.TgtRefKeyField, col);
                }
                else if (col.ColumnName.ToLower() == this._Instruction.TgtLinkScoreField.ToLower())
                {
                    fieldToColMappings.Add(this._Instruction.TgtLinkScoreField, col);
                }
            }
            TableColumn[] tgtCols = new TableColumn[fieldToColMappings.Count];
            fieldToColMappings.Values.CopyTo(tgtCols, 0);
            string spName = "BulkInsert_" + this._Instruction.TgtTable + "_SP";
            string spBody =
                StoredProcUtil.CreateStoredProcedureStatementForInsert(
                    this._Instruction.TgtDbProvider, spName, string.Empty, this._Instruction.TgtTable, tgtCols);
            DbUtil.NewStoredProc(this._Instruction.TgtConnStr, spName, spBody);

            Dictionary<string, IDataParameter> parameters = new Dictionary<string, IDataParameter>();
            foreach (string fieldName in fieldToColMappings.Keys)
            {
                if (this._Instruction.TgtDbProvider == DataProviderType.MSSQL)
                {
                    SqlParameter parameter = new SqlParameter("@" + fieldName, DBNull.Value);
                    parameters.Add(fieldName, parameter);
                }
                else
                {
                    OracleParameter parameter = new OracleParameter("@" + fieldName, DBNull.Value);
                    parameters.Add(fieldName, parameter);
                }
            }

            Dictionary<int, Dictionary<int, double>> linkages =
                new Dictionary<int, Dictionary<int, double>>();

            IDbConnection childConn = null;
            if (this._Instruction.ChildDbProvider == DataProviderType.MSSQL)
                childConn = new SqlConnection(this._Instruction.ChildConnStr);
            else
                childConn = new OracleConnection(this._Instruction.ChildConnStr);

            IDbConnection tgtConn = null;
            if (this._Instruction.TgtDbProvider == DataProviderType.MSSQL)
                tgtConn = new SqlConnection(this._Instruction.TgtConnStr);
            else
                tgtConn = new OracleConnection(this._Instruction.TgtConnStr);
            try
            {
                childConn.Open();
                IDbCommand childCmd = childConn.CreateCommand();
                childCmd.CommandType = CommandType.Text;

                tgtConn.Open();
                IDbCommand tgtCmd = tgtConn.CreateCommand();
                tgtCmd.CommandType = CommandType.StoredProcedure;

                int processedEntityCount = 0;
                int linkedEntityCount = 0;
                
                foreach (int fromEntityID in seedEntityIDs)
                {
                    if (!linkages.ContainsKey(fromEntityID))
                    {
                        Dictionary<int, double> childAssociations = new Dictionary<int, double>();
                        int hopCount = 1;
                        this.GetChildren(
                            fromEntityID, childCmd, hopCount,
                            this._Instruction.LinkageSimilarityThreshold,
                            ref childAssociations);
                        if(childAssociations !=null && childAssociations.Count>0)
                        {
                            linkedEntityCount += childAssociations.Count;
                            linkages.Add(fromEntityID, childAssociations);
                        }

                        this.SaveLinkageResult(
                            fromEntityID,
                            childAssociations, tgtCmd, 
                            this._Instruction.TgtSrcKeyField,
                            this._Instruction.TgtRefKeyField, 
                            this._Instruction.TgtLinkScoreField,
                            spName, parameters);
                            
                    }

                    processedEntityCount += 1;
                    if(this.WorkerStatusChangedEvent !=null)
                    {
                        string msg = string.Format("Processing {0} of {1} entities, found {2} linkages",
                                                   processedEntityCount, seedEntityIDs.Count, linkedEntityCount);
                        int pct = processedEntityCount * 100 / seedEntityIDs.Count;
                        this.WorkerStatusChangedEvent(this._JobTicketID, this.GetType(), this._Instruction,
                            this.WorkflowName, this.ComponentName, msg, pct);
                    }
                }

                //this.SaveLinkageResult(
                //    linkages, this._Instruction.TgtDbProvider,
                //    this._Instruction.TgtConnStr, this._Instruction.TgtTable,
                //    this._Instruction.TgtSrcKeyField,this._Instruction.TgtRefKeyField,
                //    this._Instruction.TgtLinkScoreField);

                this._Output = new EntityLinkageOutput(this._Instruction.OwnerComponentID, this._JobTicketID,
                                                       seedEntityIDs.Count, linkedEntityCount);

                if (this.WorkerFinishedEvent != null)
                    this.WorkerFinishedEvent(this._JobTicketID, this.GetType(), this._Instruction, this._Output);
            }
            catch(Exception ex)
            {
                if(this.WorkerErredEvent !=null)
                {
                    this.WorkerErredEvent(this._JobTicketID, this.GetType(), this._Instruction, ex.ToString(),true);
                }
            }
            finally
            {
                childConn.Close();
            }
        }
        #endregion

        #region parent->child
        private void GetChildren(
            int parentEntityID, IDbCommand childCmd, 
            int hopCount, double similarityThreshold,
            ref Dictionary<int,double> childAssociations)
        {
            double similarity = Math.Pow(1.0 - this._Instruction.HopFactor, hopCount);
            if (similarity < similarityThreshold)
                return;

            string sql = string.Format(this._Instruction.ChildSql, parentEntityID);
            if (childCmd.Connection.State != ConnectionState.Open)
                childCmd.Connection.Open();

            childCmd.CommandText=sql;
            IDataReader reader = childCmd.ExecuteReader();

            Dictionary<int, double> childAssociations2 = new Dictionary<int, double>();
            while(reader.Read())
            {
                int childKey = int.Parse(reader[this._Instruction.ChildPKFieldName].ToString());
                if (similarity >= similarityThreshold)
                {
                    if (childKey != parentEntityID && 
                        !childAssociations.ContainsKey(childKey) && 
                        !childAssociations2.ContainsKey(childKey))
                    {
                        childAssociations2.Add(childKey, similarity);
                    }
                }
            }
            reader.Close();

            if (childAssociations2.Count > 0)
            {
                foreach (int childKey in childAssociations2.Keys)
                {
                    if (!childAssociations.ContainsKey(childKey))
                    {
                        childAssociations.Add(childKey, childAssociations2[childKey]);
                        this.GetChildren(
                            childKey, childCmd, hopCount + 1, similarityThreshold, ref childAssociations);
                    }
                }
            }
        }
        #endregion

        #region save result
        //private void SaveLinkageResult(
        //    Dictionary<int,Dictionary<int,double>> linkages,
        //    DataProviderType tgtDbProvider,
        //    string tgtConnStr, 
        //    string tgtTable,
        //    string srcKeyField,
        //    string refKeyField,
        //    string linkScoreField)
        //{
        //    TableColumn[] cols = DbUtil.GetTableColumns(tgtDbProvider, tgtConnStr, string.Empty, tgtTable);
        //    Dictionary<string, TableColumn> fieldToColMappings = new Dictionary<string, TableColumn>();
        //    foreach(TableColumn col in cols)
        //    {
        //        if(col.ColumnName.ToLower()==srcKeyField.ToLower())
        //        {
        //            fieldToColMappings.Add(srcKeyField, col);
        //        }
        //        else if(col.ColumnName.ToLower()==refKeyField.ToLower())
        //        {
        //            fieldToColMappings.Add(refKeyField, col);
        //        }
        //        else if(col.ColumnName.ToLower()==linkScoreField.ToLower())
        //        {
        //            fieldToColMappings.Add(linkScoreField, col);
        //        }
        //    }
        //    TableColumn[] tgtCols = new TableColumn[fieldToColMappings.Count];
        //    fieldToColMappings.Values.CopyTo(tgtCols, 0);
        //    string spName = "BulkInsert_" + tgtTable + "_SP";
        //    string spBody =
        //        StoredProcUtil.CreateStoredProcedureStatementForInsert(
        //            tgtDbProvider, spName, string.Empty, tgtTable, tgtCols);
        //    DbUtil.NewStoredProc(tgtConnStr, spName, spBody);

        //    Dictionary<string, IDataParameter> parameters = new Dictionary<string, IDataParameter>();
        //    foreach(string fieldName in fieldToColMappings.Keys)
        //    {
        //        if(tgtDbProvider==DataProviderType.MSSQL)
        //        {
        //            SqlParameter parameter = new SqlParameter("@" + fieldName, DBNull.Value);
        //            parameters.Add(fieldName, parameter);
        //        }
        //        else
        //        {
        //            OracleParameter parameter = new OracleParameter("@" + fieldName, DBNull.Value);
        //            parameters.Add(fieldName, parameter);
        //        }
        //    }

        //    IDbConnection conn = null;
        //    if (tgtDbProvider == DataProviderType.MSSQL)
        //        conn = new SqlConnection(tgtConnStr);
        //    else
        //        conn = new OracleConnection(tgtConnStr);

        //    try
        //    {
        //        conn.Open();
        //        IDbCommand cmd = conn.CreateCommand();
        //        cmd.CommandType = CommandType.StoredProcedure;
        //        cmd.CommandText = spName;

        //        foreach(int srcKey in linkages.Keys)
        //        {
        //            Dictionary<int, double> refKeyScores = linkages[srcKey];
        //            foreach(int refKey in refKeyScores.Keys)
        //            {
        //                cmd.Parameters.Clear();
        //                IDataParameter parameter1 = parameters[srcKeyField];
        //                parameter1.Value = srcKey;
        //                cmd.Parameters.Add(parameter1);

        //                IDataParameter parameter2 = parameters[refKeyField];
        //                parameter2.Value = refKey;
        //                cmd.Parameters.Add(parameter2);

        //                IDataParameter parameter3 = parameters[linkScoreField];
        //                parameter3.Value = refKeyScores[refKey];
        //                cmd.Parameters.Add(parameter3);

        //                cmd.ExecuteNonQuery();
        //            }
        //        }
        //    }
        //    catch(Exception ex)
        //    {
        //        if (this.WorkerErredEvent != null)
        //            this.WorkerErredEvent(this._JobTicketID, this.GetType(), this._Instruction, ex.ToString());
        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }
        //}

        private void SaveLinkageResult(
            int srcKey,
            Dictionary<int, double> refKeyScores,
            IDbCommand cmd, 
            string srcKeyField,
            string refKeyField,
            string linkScoreField,
            string spName,
            Dictionary<string,IDataParameter> parameters)
        {
            if (cmd.Connection.State != ConnectionState.Open)
                cmd.Connection.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = spName;

            foreach (int refKey in refKeyScores.Keys)
            {
                cmd.Parameters.Clear();
                IDataParameter parameter1 = parameters[srcKeyField];
                parameter1.Value = srcKey;
                cmd.Parameters.Add(parameter1);

                IDataParameter parameter2 = parameters[refKeyField];
                parameter2.Value = refKey;
                cmd.Parameters.Add(parameter2);

                IDataParameter parameter3 = parameters[linkScoreField];
                parameter3.Value = refKeyScores[refKey];
                cmd.Parameters.Add(parameter3);

                cmd.ExecuteNonQuery();
            }
        }
        #endregion
    }
}
